package com.lan.app.dao;

import com.lan.app.repository.Tp;
import com.lan.support.page.PageUtils;
import com.lan.support.page.Paging;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class TpDao {

    private static final String tbName = "tb_tp";

    private static final String colSQL = "id,tb_id,zh,mn,dt,seq,remark,state,is_del,create_time,update_time";

    private static final String insertSQL = "INSERT INTO " + tbName + "(tb_id,zh,mn,dt,seq,remark,state,is_del,create_time,update_time) VALUES(:tbId,:zh,:mn,:dt,:seq,:remark,:state,:isDel,:createTime,:updateTime)";

    private static final String delSQL = "DELETE FROM " + tbName + " WHERE id=:id";

    private static final String selectSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE id=:id";

    private static final String isDelSQL = "UPDATE " + tbName + " SET is_del=:isDel,update_time=:updateTime WHERE id=:id";

    private static final String selectIdListSQL = "SELECT " + colSQL + " FROM " + tbName + "  WHERE id IN(:itemList)";

    private static final String selectByTbIdSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE tb_id=:tbId AND is_del=1 ORDER BY seq DESC";

    private static final String selectByTbListSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE tb_id IN(:tbIdList) AND is_del=1";


    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public int save(Tp tp) {
        SqlParameterSource ps = new BeanPropertySqlParameterSource(tp);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(insertSQL, ps, keyHolder);
        tp.setId(keyHolder.getKey().intValue());
        return keyHolder.getKey().intValue();
    }

    public int del(Integer id) {
        Map<String, Object> param = new HashMap<>();
        param.put("id", id);
        return namedParameterJdbcTemplate.update(delSQL, param);
    }

    public Integer modifyDel(Integer id, Integer isDel, Date curDate) {
        Map<String, Object> param = new HashMap();
        param.put("id", id);
        param.put("isDel", isDel);
        param.put("updateTime", curDate);
        return namedParameterJdbcTemplate.update(isDelSQL, param);
    }

    public int updateById(Tp tp) {
        StringBuilder sb = new StringBuilder("UPDATE " + tbName + " SET update_time=:updateTime");
        Map<String, Object> param = new HashMap();
        param.put("updateTime", new Date());
        if (tp.getTbId() != null) {
            sb.append(",tb_id=:tbId");
            param.put("tbId", tp.getTbId());
        }
        if (StringUtils.isNotBlank(tp.getZh())) {
            sb.append(",zh=:zh");
            param.put("zh", tp.getZh());
        }
        if (StringUtils.isNotBlank(tp.getMn())) {
            sb.append(",mn=:mn");
            param.put("mn", tp.getMn());
        }
        if (StringUtils.isNotBlank(tp.getDt())) {
            sb.append(",dt=:dt");
            param.put("dt", tp.getDt());
        }
        if (StringUtils.isNotBlank(tp.getRemark())) {
            sb.append(",remark=:remark");
            param.put("remark", tp.getRemark());
        }
        if (tp.getSeq() != null) {
            sb.append(",seq=:seq");
            param.put("seq", tp.getSeq());
        }
        if (tp.getState() != null) {
            sb.append(",state=:state");
            param.put("state", tp.getState());
        }
        sb.append(" WHERE id=:id");
        param.put("id", tp.getId());
        return namedParameterJdbcTemplate.update(sb.toString(), param);
    }

    public Tp findById(Integer id) {
        Map<String, Object> param = new HashMap();
        param.put("id", id);
        List<Tp> list = namedParameterJdbcTemplate.query(selectSQL, param, new Mapper());
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }

    public List<Tp> findByIdList(List<Integer> itemList) {
        Map<String, Object> param = new HashMap<>();
        param.put("itemList", itemList);
        return namedParameterJdbcTemplate.query(selectIdListSQL, param, new Mapper());
    }

    public Integer countByPage(Tp tp, Paging page) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(id) FROM " + tbName + " WHERE is_del=1 ");
        Map<String, Object> param = new HashMap();
        where(sb, param, tp);
        PageUtils.page(page, param, sb, false);
        return namedParameterJdbcTemplate.queryForObject(sb.toString(), param, Integer.class);
    }

    public List<Tp> findByPage(Tp tp, Paging page) {
        StringBuilder sb = new StringBuilder("SELECT " + colSQL + " FROM " + tbName + " WHERE is_del=1 ");
        Map<String, Object> param = new HashMap();
        where(sb, param, tp);
        PageUtils.page(page, param, sb, false);
        PageUtils.page(page, param, sb);
        return namedParameterJdbcTemplate.query(sb.toString(), param, new Mapper());
    }

    private void where(StringBuilder sb, Map<String, Object> param, Tp tp) {
        if (tp.getTbId() != null) {
            param.put("tbId", tp.getTbId());
            sb.append(" AND tb_id=:tbId");
        }
        if (StringUtils.isNotBlank(tp.getZh())) {
            param.put("zh", tp.getZh());
            sb.append(" AND zh=:zh");
        }
        if (StringUtils.isNotBlank(tp.getMn())) {
            param.put("mn", tp.getMn());
            sb.append(" AND mn=:mn");
        }
        if (StringUtils.isNotBlank(tp.getDt())) {
            param.put("dt", tp.getDt());
            sb.append(" AND dt=:dt");
        }
        if (tp.getState() != null) {
            param.put("state", tp.getState());
            sb.append(" AND state=:state");
        }
    }

    public List<Tp> getList(Integer tbId) {
        Map<String, Object> param = new HashMap();
        param.put("tbId", tbId);
        return namedParameterJdbcTemplate.query(selectByTbIdSQL, param, new Mapper());
    }

    public List<Tp> findList(List<Integer> tbIdList) {
        Map<String, Object> param = new HashMap<>();
        param.put("tbIdList", tbIdList);
        return namedParameterJdbcTemplate.query(selectByTbListSQL, param, new Mapper());
    }

    class Mapper implements RowMapper<Tp> {
        @Override
        public Tp mapRow(ResultSet rs, int i) throws SQLException {
            Tp item = new Tp();
            item.setId(rs.getInt("id"));
            item.setTbId(rs.getInt("tb_id"));
            item.setZh(rs.getString("zh"));
            item.setMn(rs.getString("mn"));
            item.setDt(rs.getString("dt"));
            item.setRemark(rs.getString("remark"));
            item.setSeq(rs.getInt("seq"));
            item.setState(rs.getInt("state"));
            item.setIsDel(rs.getInt("is_del"));
            item.setCreateTime(rs.getTimestamp("create_time"));
            item.setUpdateTime(rs.getTimestamp("update_time"));
            return item;
        }
    }

}
